package website.seamonster.ethegg.repository;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import website.seamonster.ethegg.dataobject.Egg;

import javax.transaction.Transactional;


/**
 * Created by 罗海量
 * 2018-07-17 19:01
 */
public interface EggRepository extends JpaRepository<Egg, Long> {
    //CAUTION: 仅做测试用！
    @Modifying
    @Transactional
    @Query("delete from Egg e where e.id=?1")
    int deleteById(Long id);

    @Query(value = "SELECT * from sys_egg order by ID DESC limit 0,1", nativeQuery = true)
    Egg findLatestEgg();

    /**
     * 查找未分配的若干只蛋，按照Id（生成的次序）排列
     * @param seq
     * @param batchNum
     * @param pageable
     * @return
     */
    Page<Egg> findBySeqAndBatchNumOrderById(Long seq, Integer batchNum, Pageable pageable);


    @Query(value = "select * from sys_egg where SEQ>=0 and BATCH_NUM>=0 and EGG_STATUS=2 and USER_ID <>?1 order by SEQ limit 0,1", nativeQuery = true)
    Egg findEggToBuy(String userId);

    Egg findFirstByEggStatus(int eggStatus);

    @Query(value = "select max(SEQ) from sys_egg", nativeQuery = true)
    Long getMaxSeq();

    Egg findFirstByEggStatusOrderBySeqDesc(Integer eggStatus);

    @Query(value = "select count(e) from Egg e where e.eggStatus=2")
    Long getSellingEggNum();


    /**
     * CAUTION：谨慎使用，如果oldEggStatus不是1的话，很有可能修改脏数据，
     * 这时建议使用锁更新某批次所有待售中的蛋的状态
     * @param eggStatus
     * @param oldEggStatus  如果oldEggStatus不是1的话，很有可能修改脏数据
     * @param batchNum
     * @return
     */
    @Modifying
    @Transactional
    @Query(value = "update Egg e set e.eggStatus=?1 where e.eggStatus=?2 and e.batchNum=?3")
    int updateEggStatusByBatch(Integer eggStatus, Integer oldEggStatus, Integer batchNum);

    //查找某批次，某状态的蛋
    Page<Egg> findByEggStatusAndBatchNumOrderBySeqAsc(Integer eggStatus, Integer batchNum, Pageable pageable);

    @Query("select count(e) from Egg e where e.userId=?1 and e.eggStatus=?2")
    Integer getBroodEggsCountByUserId(String userId, Integer eggStatusBrood);
}
